Questions

8 of 15

1What are the main categories of data types available in MySQL?

2What is the difference between CHAR and VARCHAR data types?

3Which data type would you use to store dates and times in MySQL?

4What is the difference between INT, FLOAT, and DECIMAL data types?

5What is the use of the TEXT and BLOB data types, and how are they different from VARCHAR?

6How does MySQL handle precision and scale in DECIMAL(M, D) columns internally, and how do these differ from FLOAT and DOUBLE in terms of storage and accuracy?

7When storing time zone–aware data, what are the differences in behavior and use cases between DATETIME, TIMESTAMP, and CONVERT_TZ() in MySQL?

8If you define a VARCHAR(255) column with utf8mb4 encoding, how does MySQL calculate the maximum possible storage size for that column, and how does it differ from CHAR(255)?

9What are the advantages and limitations of using ENUM and SET data types in terms of performance, flexibility, and schema evolution?

10Explain how MySQL internally stores and sorts values of type BLOB and TEXT. What happens when you try to index a TEXT column?

11How do signed and unsigned integer types affect query results, index usage, and storage size? Can you demonstrate an example where overflow behavior differs?

12In what scenarios would using a JSON column be preferable to a normalized table structure, and what are the internal storage and indexing implications of JSON in MySQL 8.0?

13How does MySQL’s BIT(M) type differ from BOOLEAN, TINYINT(1), and binary string types (BINARY, VARBINARY) in terms of storage, representation, and retrieval?

14If you define a composite index on multiple columns of different data types (e.g., INT, VARCHAR, and DATE), how do the internal data type differences influence sorting, comparisons, and index efficiency?

15What are the practical implications of using CHAR vs VARCHAR for columns in InnoDB tables with varying row lengths and frequent updates? How does this choice affect row fragmentation and performance?

mysql

Functions and Operators

08 / 15

If you define a VARCHAR(255) column with utf8mb4 encoding, how does MySQL calculate the maximum possible storage size for that column, and how does it differ from CHAR(255)?

Storage Calculation for VARCHAR(255) vs CHAR(255) with utf8mb4

MySQL calculates storage for VARCHAR and CHAR differently, especially when using utf8mb4, which requires up to 4 bytes per character.

Storage Calculation for VARCHAR(255) with utf8mb4

1
utf8mb4 uses up to 4 bytes per character.
2
Maximum possible data storage = 255 × 4 = 1020 bytes.
3
VARCHAR also requires 1 or 2 bytes for length storage.
4
Because 1020 > 255, VARCHAR(255) uses 2 bytes for length.
5
Total max storage = 1020 bytes + 2 bytes = 1022 bytes.

Storage Calculation for CHAR(255) with utf8mb4

1
CHAR is fixed-length and always allocates full size.
2
Each character may need up to 4 bytes in utf8mb4.
3
Total storage = 255 × 4 = 1020 bytes (always reserved).
4
CHAR adds 1 byte for padding metadata internally.
5
CHAR wastes more space but is faster for fixed-length lookups.

Key Differences

1
VARCHAR stores variable-length data with 1–2 bytes overhead; CHAR always stores fixed-length.
2
VARCHAR(255) max size = 1022 bytes; CHAR(255) always uses 1020 bytes.
3
CHAR is less space-efficient with utf8mb4 because every row reserves the full 255 characters × 4 bytes.
4
VARCHAR is preferred for variable-length text; CHAR is ideal for fixed-size data like hashes or codes.